package com.jeehentor.generate.service.impl;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jeehentor.common.exception.HentorException;
import com.jeehentor.generate.dto.*;
import com.jeehentor.generate.entity.SysGenForm;
import com.jeehentor.generate.entity.SysGenModel;
import com.jeehentor.generate.entity.SysGenModelField;
import com.jeehentor.generate.generate.constants.GenerateFileType;
import com.jeehentor.generate.generate.dbGenerate.DbGenerateFactory;
import com.jeehentor.generate.generate.enums.GenerateModelType;
import com.jeehentor.generate.generate.enums.GenerateMysqlType;
import com.jeehentor.generate.generate.fileGenrate.FileGenerateFactory;
import com.jeehentor.generate.mapper.SysGenFormMapper;
import com.jeehentor.generate.mapper.SysGenModelMapper;
import com.jeehentor.generate.service.ISysGenModelFieldService;
import com.jeehentor.generate.service.ISysGenModelService;
import com.jeehentor.generate.utils.KeywordValidator;
import com.jeehentor.generate.vo.GenerateCodeVO;
import com.jeehentor.utils.SecurityUtil;
import com.jeehentor.utils.SpringContextUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * <p>
 * 模型配置主表 服务实现类
 * </p>
 *
 * @author wzd
 * @since 2024-05-10
 */
@Service
@Slf4j
@RequiredArgsConstructor
public class SysGenModelServiceImpl extends ServiceImpl<SysGenModelMapper, SysGenModel> implements ISysGenModelService {

    private final ISysGenModelFieldService sysGenModelFieldService;
    private final SysGenModelMapper sysGenModelMapper;
    private final DataSource dataSource;
    private final SysGenFormMapper sysGenFormMapper;


    @Override
    @Transactional(rollbackFor = Exception.class)
    public void saveModel(GenModelDTO dto) {
        //字段信息
        List<SysGenModelField> generateDevDetail = dto.getFieldList();
        if(StringUtils.isBlank(dto.getTableName())){
            throw new HentorException("保存失败，表名不能为空！");
        }
        //校验表名是否存在
        LambdaQueryWrapper<SysGenModel> sysGenModelLambdaQueryWrapper = new LambdaQueryWrapper<>();
        sysGenModelLambdaQueryWrapper.eq(SysGenModel::getTableName, dto.getTableName());
        List<SysGenModel> list = list(sysGenModelLambdaQueryWrapper);
        if(CollectionUtils.isNotEmpty(list)){
            throw new HentorException(String.format("保存失败，表名%s已存在！", dto.getTableName()));
        }
        //校验字段
        checkColumnName(generateDevDetail);
        //保存主表信息
        dto.setCreateBy(SecurityUtil.getCurrentUser().getId());
        save(dto);

        //排序不能重复
        generateDevDetail.forEach(x->{
            //关联主表id
            x.setModelId(dto.getId());
            if(StringUtils.isBlank(x.getFieldName())){
                throw new HentorException("保存失败，字段名不能为空！");
            }

        });
        sysGenModelFieldService.saveBatch(generateDevDetail);
    }

    private void checkColumnName(List<SysGenModelField> genModelFields){
        //校验字段名不能为关键字
        List<String> keyWordList = genModelFields.stream().map(SysGenModelField::getFieldName).filter(KeywordValidator::isKeyword).collect(Collectors.toList());
        if(CollectionUtils.isNotEmpty(keyWordList)){
            throw new HentorException("字段名:"+String.join(",",keyWordList)+"不能为关键字！");
        }
        //字段名不能重复
        Map<String, List<SysGenModelField>> collect = genModelFields.stream().collect(Collectors.groupingBy(SysGenModelField::getFieldName));
        List<Map.Entry<String, List<SysGenModelField>>> collect1 = collect.entrySet().stream().filter(x -> collect.get(x.getKey()).size() > 1).collect(Collectors.toList());
        if(CollectionUtils.isNotEmpty(collect1)){
            throw new HentorException(String.format("字段名%s重复！",collect1.get(0).getKey()));
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void updateModel(GenModelDTO dto) {
        checkColumnName(dto.getFieldList());
        LambdaQueryWrapper<SysGenModel> sysGenModelLambdaQueryWrapper = new LambdaQueryWrapper<>();
        sysGenModelLambdaQueryWrapper.eq(SysGenModel::getTableName, dto.getTableName());
        SysGenModel sysGenModel = this.getOne(sysGenModelLambdaQueryWrapper);
        if(sysGenModel != null && !sysGenModel.getId().equals(dto.getId())){
            throw new HentorException("模型已存在");
        }
        SysGenModel byId = getById(dto);
        assert byId != null;
        if(!dto.getVersionCode().equals(byId.getVersionCode())){
            throw new HentorException("模型版本冲突，请重试!");
        }
        BigDecimal number = new BigDecimal(byId.getVersionCode());
        BigDecimal add = number.add(new BigDecimal("0.1"));
        String newVer = String.valueOf(add);
        dto.setVersionCode(newVer);
        dto.setUpdateBy(SecurityUtil.getCurrentUser().getId());
        dto.setSyncStatus("0");
        updateById(dto);
        if(CollectionUtils.isNotEmpty(dto.getFieldList())){
            List<SysGenModelField> collect = dto.getFieldList().stream().peek(item -> {
                item.setUpdateBy(SecurityUtil.getCurrentUser().getId());
            }).collect(Collectors.toList());
            collect.forEach(x->{
                UpdateWrapper<SysGenModelField> updateWrapper = new UpdateWrapper<>();
                updateWrapper.eq("id", x.getId()).set("field_name",x.getFieldName()).set("field_type", x.getFieldType()).set("field_point", x.getFieldPoint()).set("length", x.getLength())
                        .set("default_value", x.getDefaultValue()).set("nullable",x.getNullable()).set("is_required",x.getIsRequired()).set("primary_key",x.getPrimaryKey())
                        .set("sort_order",x.getSortOrder()).set("is_unique", x.getIsUnique()).set("column_width", x.getColumnWidth()).set("form_module", x.getFormModule())
                        .set("search_span", x.getSearchSpan()).set("search_module", x.getSearchModule()).set("is_show",x.getIsShow()).set("is_form", x.getIsForm())
                        .set("is_query", x.getIsQuery()).set("remarks", x.getRemarks()).set("update_by",x.getUpdateBy());
                sysGenModelFieldService.update(updateWrapper);
            });
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void delModel(GenModelDTO dto) {
        LambdaQueryWrapper<SysGenForm> sysGenFormLambdaQueryWrapper = new LambdaQueryWrapper<>();
        sysGenFormLambdaQueryWrapper.eq(SysGenForm::getModelId, dto.getId());
        List<SysGenForm> sysGenForms = sysGenFormMapper.selectList(sysGenFormLambdaQueryWrapper);
        if(CollectionUtils.isNotEmpty(sysGenForms)){
            throw new HentorException("模型已关联表单，无法删除");
        }
        LambdaQueryWrapper<SysGenModelField> sysGenModelFieldLambdaQueryWrapper = new LambdaQueryWrapper<>();
        sysGenModelFieldLambdaQueryWrapper.eq(SysGenModelField::getModelId, dto.getId());
        List<SysGenModelField> sysGenModelFields = sysGenModelFieldService.list(sysGenModelFieldLambdaQueryWrapper);
        if(CollectionUtils.isNotEmpty(sysGenModelFields)){
            sysGenModelFieldService.removeBatchByIds(sysGenModelFields);
        }
        removeById(dto);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void syncDataBase(GenModelDTO dto) throws SQLException {
        Connection connection = dataSource.getConnection();
        try {
            log.info("=========================同步数据库：开始同步数据结构=========================");
            GenerateDbDTO generateDbDTO = new GenerateDbDTO();
            //暂时只支持mysql
            generateDbDTO.setDbType(DbType.MYSQL);
            String url = connection.getMetaData().getURL();
            if(url.contains(DbType.MYSQL.getDb())){
                generateDbDTO.setDbType(DbType.MYSQL);
            }else {
                throw new HentorException("当前只支持mysql数据库");
            }
            //查询表信息
            SysGenModel byId = getById(dto);
            if(byId == null){
                throw new HentorException("同步失败，模型配置信息不存在！");
            }
            LambdaQueryWrapper<SysGenModelField> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(SysGenModelField::getModelId, byId.getId());
            List<SysGenModelField> list = sysGenModelFieldService.list(queryWrapper);
            generateDbDTO.setSysGenModel(byId);
            generateDbDTO.setFieldList(list);
            String[] a = new String[]{"TABLE"};
            ResultSet tables = connection.getMetaData().getTables(connection.getCatalog(), null, byId.getTableName(), a);
            //判断表名是否存在
            if(tables.next()){
                //修改表备注
                generateDbDTO.setModel(GenerateModelType.UPDATE_TABLE_COMMENT.getCode());
                //获取ddl语句
                List<String> generate = DbGenerateFactory.generate(generateDbDTO);
                List<SysGenModelField> sysGenModelFieldList = getChangeColumnList(connection, generateDbDTO);
                //获取要修改的列信息
                generateDbDTO.setFieldList(sysGenModelFieldList);
                //更新字表信息
                sysGenModelFieldService.updateBatchById(sysGenModelFieldList);
                generateDbDTO.setModel(GenerateModelType.UPDATE_COLUMN_NAME.getCode());
                generate.addAll(DbGenerateFactory.generate(generateDbDTO));
                //修改字段属性
                generateDbDTO.setModel(GenerateModelType.UPDATE_COLUMN_TYPE.getCode());
                generateDbDTO.setFieldList(sysGenModelFieldService.list(queryWrapper));
                generate.addAll(DbGenerateFactory.generate(generateDbDTO));
                //判断主键是否有修改
                ArrayList<String> oldPrimaryKeyList = new ArrayList<>();
                ResultSet oldPrimaryKeys = connection.getMetaData().getPrimaryKeys(connection.getCatalog(), null, generateDbDTO.getSysGenModel().getTableName());
                while (oldPrimaryKeys.next()){
                    String columnName = oldPrimaryKeys.getString("COLUMN_NAME");
                    oldPrimaryKeyList.add(columnName);
                }
                List<String> newPrimaryKeyList = generateDbDTO.getFieldList().stream().filter(x -> "1".equals(x.getPrimaryKey()) ).map(SysGenModelField::getFieldName).collect(Collectors.toList());
                ;
                boolean flag = org.apache.commons.collections4.CollectionUtils.isEqualCollection(oldPrimaryKeyList,newPrimaryKeyList);
                if(!flag){
                    if(CollectionUtils.isNotEmpty(oldPrimaryKeyList)){
                        //删除主键
                        generateDbDTO.setModel(GenerateModelType.DELETE_PRIMARY_KEY.getCode());
                        //获取ddl语句
                        generate.addAll(DbGenerateFactory.generate(generateDbDTO));
                    }
                    //创建主键
                    generateDbDTO.setModel(GenerateModelType.CREATE_PRIMARY_KEY.getCode());
                    //获取ddl语句
                    generate.addAll(DbGenerateFactory.generate(generateDbDTO));
                }
                generate.forEach(sysGenModelMapper::generateDll);
            }else {
                //创建表信息
                generateDbDTO.setModel(GenerateModelType.CREATE_TABLE.getCode());
                //获取ddl语句
                List<String> generate = DbGenerateFactory.generate(generateDbDTO);
                sysGenModelMapper.generateDllList(generate);
            }
            byId.setSyncStatus("1");
            updateById(byId);
            log.info("=========================同步数据库：同步成功=========================");
        }catch (Exception e){
            log.error("=========================同步数据库：同步失败=========================",e);
            throw new HentorException(e.getMessage());
        }finally {
            connection.close();
        }

    }


    /**
     * 获取变化的列信息
     * 先按照顺序匹配元数据oldval和页面录入的数据newval，按顺序分组放到一个list中，循环list，判断newval=oldval的拿出来，代表这些字段不需要做任何结构操作，但需要修改属性
     * @param connection
     * @param generateDbDTO
     * @return
     * @throws SQLException
     */
    private List<SysGenModelField> getChangeColumnList(Connection connection, GenerateDbDTO generateDbDTO) throws SQLException {
        //通过数据库元数据获取列信息
        ResultSet columns = connection.getMetaData().getColumns(connection.getCatalog(), null, generateDbDTO.getSysGenModel().getTableName(), null);
        ArrayList<SysGenModelField> generateDevColumns = new ArrayList<>();
        int i = 1;
        while (columns.next()){
            SysGenModelField generateDevColumn = new SysGenModelField();
            String columnName = columns.getString("COLUMN_NAME");
            generateDevColumn.setOldFieldName(columnName);
            generateDevColumn.setSortOrder(i);
            i++;
            generateDevColumns.add(generateDevColumn);
        }
        //合并新旧数据集合
        generateDevColumns.addAll(generateDbDTO.getFieldList());
        Map<Integer, List<SysGenModelField>> collect = generateDevColumns.stream().collect(Collectors.groupingBy(SysGenModelField::getSortOrder));
        ArrayList<SysGenModelField> generateDevDetails = new ArrayList<>();
        collect.keySet().forEach(x->{
            SysGenModelField generateDevDetail = new SysGenModelField();
            collect.get(x).forEach(y->{
                if (StringUtils.isNotBlank(y.getOldFieldName()) && StringUtils.isBlank(y.getFieldName())){
                    generateDevDetail.setOldFieldName(y.getOldFieldName());
                    if(generateDevDetail.getSortOrder() == null){
                        generateDevDetail.setSortOrder(y.getSortOrder());
                    }
                }else {
                    BeanUtils.copyProperties(y, generateDevDetail, "oldColumnName","sortOrder");
                    if(generateDevDetail.getSortOrder() == null){
                        generateDevDetail.setSortOrder(y.getSortOrder());
                    }
                }
            });
            generateDevDetails.add(generateDevDetail);
        });
        ArrayList<SysGenModelField> notInGenerateDevDetails = new ArrayList<>();
        generateDbDTO.getFieldList().forEach(x->{
            generateDevColumns.forEach(y->{
                if(x.getFieldName().equals(y.getOldFieldName())){
                    notInGenerateDevDetails.add(x);
                }
            });
        });
        generateDevDetails.removeAll(notInGenerateDevDetails);
        return generateDevDetails;
    }

    @Override
    public List<String> codeFileGenerator(GenerateCodeDTO generateCodeDTO) {
        GenerateCodeVO generateCodeVO = new GenerateCodeVO();
        SysGenModel byId = getById(generateCodeDTO.getMainId());
        if(byId == null){
            throw new HentorException("模型配置信息不存在！");
        }
        LambdaQueryWrapper<SysGenModelField> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(SysGenModelField::getModelId,generateCodeDTO.getMainId());
        List<SysGenModelField> list = sysGenModelFieldService.list(queryWrapper);
        GenerateFileDTO generateFileDTO = new GenerateFileDTO();
        generateFileDTO.setPackagePath(generateCodeDTO.getPackagePath());
        generateFileDTO.setFileType(GenerateFileType.JAVA_FILE);
        generateFileDTO.setSysGenModel(byId);
        generateFileDTO.setModuleName(generateCodeDTO.getModuleName());
        generateFileDTO.setFieldList(list);
        //生成java后端代码
        List<String> javaGenerate = FileGenerateFactory.generate(generateFileDTO);
        generateFileDTO.setFileType(GenerateFileType.VUE_FILE);
        //前端代码
        List<String> vueGenerate = FileGenerateFactory.generate(generateFileDTO);
        UUID uuid = UUID.randomUUID();
        String uuidString = uuid.toString().replace("-", "");
        generateCodeVO.setFileKey(uuidString);
        generateCodeVO.setJavaPathList(javaGenerate);
        generateCodeVO.setVuePathList(vueGenerate);
        List<String> combined = new ArrayList<>();
        combined.addAll(javaGenerate);
        combined.addAll(vueGenerate);
        return combined;
    }


    @Override
    public void importSql(GenerateSqlDTO dto) {
        String sql = dto.getSql();
        GenModelDTO genModelDTO = new GenModelDTO();

        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            if (statement instanceof CreateTable) {
                CreateTable createTable = (CreateTable) statement;
                if(StringUtils.isNotBlank(createTable.getTable().getName())){
                    Pattern commentPattern = Pattern.compile("`(.*?)`");
                    Matcher commentMatcher = commentPattern.matcher(createTable.getTable().getName());
                    if(commentMatcher.find()){
                        genModelDTO.setTableName(commentMatcher.group(1));
                    }else {
                        genModelDTO.setTableName(createTable.getTable().getName());
                    }
                }

                Pattern commentPattern = Pattern.compile("COMMENT='([^']+)'");
                Matcher commentMatcher = commentPattern.matcher(sql);
                if (commentMatcher.find()) {
                    genModelDTO.setTableComment(commentMatcher.group(1));
                } else {
                    genModelDTO.setTableComment("");
                }

                List<SysGenModelField> columns = new ArrayList<>();
                int order = 0;
                for (ColumnDefinition columnDef : createTable.getColumnDefinitions()) {
                    order++;
                    SysGenModelField tableColumn = new SysGenModelField();
                    tableColumn.setSortOrder(order);
                    if(StringUtils.isNotBlank(columnDef.getColumnName())){
                        Pattern pattern = Pattern.compile("`(.*?)`");
                        Matcher matcher = pattern.matcher(columnDef.getColumnName());
                        if(matcher.find()){
                            tableColumn.setFieldName(matcher.group(1));
                        }else {
                            tableColumn.setFieldName(columnDef.getColumnName());
                        }
                    }
                    tableColumn.setFieldType(GenerateMysqlType.getCodeByDesc(columnDef.getColDataType().getDataType()));
                    Integer length = null;
                    if (columnDef.getColDataType().getArgumentsStringList() != null && !columnDef.getColDataType().getArgumentsStringList().isEmpty()) {
                        try {
                            length = Integer.parseInt(columnDef.getColDataType().getArgumentsStringList().get(0));
                        } catch (NumberFormatException e) {
                            length = null; // Length is not a valid integer, set to null or handle appropriately
                        }
                    }
                    tableColumn.setLength(length);

                    String defaultValue = null;
                    String comment = null;
                    List<String> specs = columnDef.getColumnSpecs();
                    if (CollectionUtils.isNotEmpty(specs)) {
                        for (int i = 0; i < specs.size(); i++) {
                            if (specs.get(i).toUpperCase().startsWith("DEFAULT")) {
                                if(i+1 < specs.size()){
                                    if(!"NULL".equals(specs.get(i+1))){
                                        defaultValue = specs.get(i+1);
                                    }
                                }
                            } else if (specs.get(i).toUpperCase().startsWith("COMMENT")) {
                                if(i+1 < specs.size()){
                                    if(!"NULL".equals(specs.get(i+1))){
                                        if(StringUtils.isNotBlank(specs.get(i+1))){
                                            Pattern pattern = Pattern.compile("'(.*?)'");
                                            Matcher matcher = pattern.matcher(specs.get(i+1));
                                            if(matcher.find()){
                                                comment = matcher.group(1);
                                            }else {
                                                comment = specs.get(i+1);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    tableColumn.setFormModule("Input");
                    tableColumn.setSearchModule("Input");
                    tableColumn.setDefaultValue(defaultValue);
                    if(comment != null){
                        tableColumn.setRemarks(comment);
                    }
                    tableColumn.setNullable(specs != null && specs.contains("NOT NULL") ? "0" : "1");

                    columns.add(tableColumn);
                }
                genModelDTO.setFieldList(columns);
                saveModel(genModelDTO); // Ensure this method is implemented
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public GenModelDTO selectModelById(GenModelDTO dto) {
        GenModelDTO genModelDTO = new GenModelDTO();
        SysGenModel byId = this.getById(dto);
        BeanUtils.copyProperties(byId, genModelDTO);
        LambdaQueryWrapper<SysGenModelField> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(SysGenModelField::getModelId, dto.getId());
        queryWrapper.orderByAsc(SysGenModelField::getSortOrder);
        List<SysGenModelField> list = sysGenModelFieldService.list(queryWrapper);
        genModelDTO.setFieldList(list);
        return genModelDTO;
    }

    /**
     * 获取数据库连接
     * @return
     */
    private Connection getConnection() throws SQLException {
        DataSource dataSource = SpringContextUtils.getApplicationContext().getBean(DataSource.class);
        return dataSource.getConnection();
    }
}
